package com.such.kit.io.simple;

import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.format.CellFormat;
import org.apache.poi.ss.format.CellFormatResult;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import com.such.kit.Callable;
import com.such.kit.Logger;
import com.such.kit.datacast.CastCommons;
import com.such.kit.easyparam.ParamMap;
import com.such.kit.io.bean.ExcelIOConfig;
import com.such.kit.io.simple.support.IOSimple;
import com.such.kit.io.support.IOSource;
import com.such.kit.placeholder.PlaceholderCommons;
import com.such.kit.stream.StreamCommons;
import com.such.kit.string.StringCommons;
import com.such.kit.validate.ValidateCommons;

/**
 * @author SUCH
 * <pre>
 * [继承扩展于 {@link IOSimple}] 的 Excel 类型 IO 工具类
 * ExcelIOSimple 通过 {@link ExcelIOConfig} 配置 IO 特性
 * 
 * [保留字] 列表
 *   {@link RESERVED_WORD_ROW_NUMBER}
 * </pre>
 * @see IOSimple
 */
public class ExcelIOSimple extends IOSimple {

	/** DEFAULT_CELL_STYLE_HEAD：默认的表头，边框、居中、加粗 */
	public static final String DEFAULT_CELL_STYLE_HEAD = "HEAD";
	/** DEFAULT_CELL_STYLE_STRING：默认的字符串，边框、居左 */
	public static final String DEFAULT_CELL_STYLE_STRING = "STRING";
	/** DEFAULT_CELL_STYLE_INTEGER：默认的整数，边框、居右 */
	public static final String DEFAULT_CELL_STYLE_INTEGER = "INTEGER";
	/** DEFAULT_CELL_STYLE_NUMBER：默认的数字，边框、居右 */
	public static final String DEFAULT_CELL_STYLE_NUMBER = "NUMBER";
	/** DEFAULT_CELL_STYLE_PERCENT：默认的百分比，边框、居右 */
	public static final String DEFAULT_CELL_STYLE_PERCENT = "PERCENT";
	/** DEFAULT_CELL_STYLE_MONEY：默认的千分位金额，边框、居右 */
	public static final String DEFAULT_CELL_STYLE_MONEY = "MONEY";
	public static final String FILE_TYPE_XLS = "xls";
	public static final String FILE_TYPE_XLSX = "xlsx";
	/** RESERVED_WORD_ROW_NUMBER：[保留字] [导入时] 在每条导入数据中添加此条数据在 Excel 中的行号 */
	public static final String RESERVED_WORD_ROW_NUMBER = "ROW_NUMBER";

	/** sheetNameRule：IO 时 sheetName 的生成、解析规则 */
	public static String sheetNameRule = "#[SHEET_NAME]_#[NUMBER]";
	/** sheetNameInterKey：对应 {@link ExcelIOConfig#sheetName} */
	public static String sheetNameInterKey = "SHEET_NAME";
	/** sheetNameNumberInterKey：序号 */
	public static String sheetNameNumberInterKey = "NUMBER";

	private static DataFormatter dataFormatter = new DataFormatter();

	/** fileType：文件类型 [参考 FILE_TYPE_*] */
	private String fileType;
	private List<ExcelIOConfig> configs;
	/** configIndexMap：{@link ExcelIOConfig#sheetName} 与 {@link #configs} 中个 {@link ExcelIOConfig} 位置的映射关注 */
	private Map<String, Integer> configIndexMap;
	/** defaultConfigIndex：默认 {@link ExcelIOConfig} 在 {@link #configs} 中的位置 */
	private int defaultConfigIndex = -1;

	/**
	 * @param fileType 文件类型 [参考 FILE_TYPE_*]
	 * @param configs 配置集
	 * @throws Exception
	 */
	public ExcelIOSimple(String fileType, ExcelIOConfig... configs) throws Exception {
		this.fileType = fileType;
		if (ValidateCommons.isNotEmpty(configs)) {
			this.configs = new ArrayList<ExcelIOConfig>();
			for (ExcelIOConfig config : configs) {
				Workbook templateWorkbook = config.getTemplateWorkbook();
				// 构建 templateWorkbook
				if (ValidateCommons.isEmpty(templateWorkbook)) {
					String sheetName = config.getSheetName();
					List<String> titleTemplate = config.getTitleTemplate();
					List<String> rowTemplate = config.getRowTemplate();
					// templateWorkbook 为空且 rowTemplate 为空则为无效的 Config
					if (ValidateCommons.isEmpty(rowTemplate)) {
						continue;
					}

					if (FILE_TYPE_XLS.equals(fileType)) {
						templateWorkbook = new HSSFWorkbook();
					} else if (FILE_TYPE_XLSX.equals(fileType)) {
						templateWorkbook = new XSSFWorkbook();
					}
					Sheet templateSheet = templateWorkbook.createSheet(sheetName);
					int rowSize = rowTemplate.size();
					int dataAreaIndex = 0;
					/*
					 * 构建表头
					 * 当表头存在时则认为 Excel 模板有 2 列，第 1 列为表头，第 2 列为数据行模板（dataAreaIndex = 1）
					 */
					if (ValidateCommons.isNotEmpty(titleTemplate)) {
						dataAreaIndex = 1;
						Row templateRow = templateSheet.createRow(0);
						int titleSize = titleTemplate.size();
						if (titleSize == rowSize) {
							for (int i = 0; i < titleSize; i++) {
								Cell templateCell = templateRow.createCell(i);
								String title = titleTemplate.get(i);
								if (title != null) {
									templateCell.setCellValue(title);
									templateCell.setCellStyle(getDefaultCellStyle(templateWorkbook, ExcelIOSimple.DEFAULT_CELL_STYLE_HEAD));
								}
							}
						} else {
							Logger.warn(getClass(), "表头列数与数据列数不相等，生成空表头");
						}
					}
					// 构建数据行模板
					Row templateRow = templateSheet.createRow(dataAreaIndex);
					for (int i = 0; i < rowSize; i++) {
						Cell templateCell = templateRow.createCell(i);
						String value = rowTemplate.get(i);
						if (value != null) {
							templateCell.setCellValue(value);
							templateCell.setCellStyle(getDefaultCellStyle(templateWorkbook));
						}
					}
					config.setMaxRow(dataAreaIndex + 1);
					config.setDataArea(dataAreaIndex + 1);
					config.setMaxCol(rowSize);
					config.setTemplateWorkbook(templateWorkbook);
					this.configs.add(config);
				} else {
					this.configs.add(config);
				}
			}

			this.configIndexMap = new HashMap<String, Integer>();
			for (int i = 0; i < this.configs.size(); i++) {
				ExcelIOConfig config = this.configs.get(i);

				this.configIndexMap.put(config.getSheetName(), i);
				if (config.isDefaultConfig()) {
					defaultConfigIndex = i;
				}
			}
		}
	}

	@Override
	public String getFileType() {
		return this.fileType;
	}

	private ExcelIOConfig getConfig(String sheetName) {
		ExcelIOConfig config = null;
		if (ValidateCommons.isNotEmpty(this.configs)) {
			Integer index = this.configIndexMap.get(sheetName);
			if (ValidateCommons.isEmpty(index)) {
				Logger.warn(getClass(), StringCommons.merger("未找到 [", sheetName, "] 对应的配置"));
				Map<String, String> sheetNameParts = PlaceholderCommons.reversalPlaceholder(sheetNameRule, sheetName);
				if (sheetNameParts.size() > 0) {
					sheetName = sheetNameParts.get(sheetNameInterKey);
					index = this.configIndexMap.get(sheetName);
					Logger.warn(getClass(), StringCommons.merger("开始查找 [", sheetName, "] 对应的配置"));
				}
			}
			if (ValidateCommons.isEmpty(index)) {
				Logger.warn(getClass(), StringCommons.merger("未找到 [", sheetName, "] 对应的配置"));
				if (defaultConfigIndex > -1) {
					config = this.configs.get(defaultConfigIndex);
					Logger.warn(getClass(), StringCommons.merger("已开启 DEFAULT_CONFIG，使用配置 [", config.getSheetName(), "]"));
				}
			} else {
				config = this.configs.get(index);
			}
		}
		return config;
	}

	@Override
	public void input(IOSource ioSource, InputStream resourceIn) throws Exception {
		if (ValidateCommons.isNotEmpty(this.configs)) {
			try {
				Workbook resourceWorkbook = null;
				if (FILE_TYPE_XLS.equals(getFileType())) {
					resourceWorkbook = new HSSFWorkbook(resourceIn);
				} else if (FILE_TYPE_XLSX.equals(getFileType())) {
					resourceWorkbook = new XSSFWorkbook(resourceIn);
				}
				int sheetSize = resourceWorkbook.getNumberOfSheets();
				if (sheetSize > 0) {
					for (int i = 0; i < sheetSize; i++) {
						Sheet resourceSheet = resourceWorkbook.getSheetAt(i);
						String sheetName = resourceSheet.getSheetName();
						ExcelIOConfig config = getConfig(sheetName);
						if (ValidateCommons.isEmpty(config)) {
							continue;
						}

						int maxRow = config.getMaxRow();
						int maxCol = config.getMaxCol();
						int dataArea = config.getDataArea();
						Workbook templateWorkbook = config.getTemplateWorkbook();
						List<String> ieList = config.getInputExpression();
						Row templateRow = null;
						if (ValidateCommons.isNotEmpty(templateWorkbook)) {
							Sheet templateSheet = templateWorkbook.getSheetAt(0);
							if (ValidateCommons.isNotEmpty(templateSheet)) {
								templateRow = templateSheet.getRow(dataArea - 1);
							}
						}
						if (ValidateCommons.isNotEmpty(templateRow)) {
							// 总行数
							int rowTotal = resourceSheet.getLastRowNum() + 1;
							// 数据开始行，既为数据区域
							int rowStart = dataArea - 1;
							// 数据结束行，总行数 - (模板最大行 - 数据区域)，其中 (模板最大行 - 数据区域) 作用为排除数据区域外的标题（标尾）
							int rowEnd = rowTotal - (maxRow - dataArea);
							// 数据开始列，从 0 开始
							int colStart = 0;
							// 数据结束列，实际的结束列会根据 maxCol 的值发生变化
							int colEnd = 0;
							for (int j = rowStart; j < rowEnd; j++) {
								Map<String, Object> fieldValueMap = new HashMap<String, Object>(super.args);
								fieldValueMap.put(RESERVED_WORD_ROW_NUMBER, String.valueOf(j + 1));
								Row resourceRow = resourceSheet.getRow(j);
								if (ValidateCommons.isNotEmpty(resourceRow)) {
									// 确认 colEnd 的实际值
									if (maxCol == 0) {
										colEnd = resourceRow.getLastCellNum();
									} else {
										colEnd = maxCol;
									}
									boolean emptyRow = true;
									for (int k = colStart; k < colEnd; k++) {
										Cell templateCell = templateRow.getCell(k);
										Cell resourceCell = resourceRow.getCell(k);
										if (ValidateCommons.isNotEmpty(templateCell) && ValidateCommons.isNotEmpty(resourceCell)) {
											String field = getCellValue(templateCell);
											field = PlaceholderCommons.getInnerKey(field);
											String value = getCellValue(resourceCell);
											fieldValueMap.put(field, value);
											// 只要有 1 个 templateCell、resourceCell 不为空则认为 resourceRow 为有效行
											if (emptyRow) {
												emptyRow = false;
											}
										}
									}
									// 校验是否为有效行
									if (!emptyRow) {
										for (String ie : ieList) {
											ioSource.doInput(ie, fieldValueMap, false, new HashMap<String, Object>());
										}
									}
								}
							}
						}
					}
				}
				ioSource.doInput(null, null, true, null);
			} finally {
				StreamCommons.close(resourceIn);
			}
		}
	}

	@Override
	public void output(IOSource ioSource, OutputStream resourceOut) throws Exception {
		if (ValidateCommons.isNotEmpty(this.configs)) {
			Workbook workbookOut = null;
			if (FILE_TYPE_XLS.equals(getFileType())) {
				workbookOut = new HSSFWorkbook();
			} else if (FILE_TYPE_XLSX.equals(getFileType())) {
				workbookOut = new XSSFWorkbook();
			}
			for (ExcelIOConfig config : this.configs) {
				String oe = config.getOutputExpression();
				OutputCallable caller = new OutputCallable(config, workbookOut);
				ioSource.doOutput(oe, super.args, caller);
				// 输出最后一个 sheetOut 的模板尾
				caller.outTemplateTail();
			}
			workbookOut.write(resourceOut);
		}
	}

	private class OutputCallable implements Callable<Map<String, Object>, Boolean> {

		private ExcelIOConfig config;
		private Workbook workbookOut;
		private Sheet sheetOut;
		private Row rowOut;
		private int sheetIndex = 0;
		private int rowIndex = 0;
		private int sheetRowIndex = 0;
		private List<CellStyle> styles;
		private Set<CellRangeAddress> cellRangeAddresses;

		public OutputCallable(ExcelIOConfig config, Workbook workbookOut) {
			this.config = config;
			this.workbookOut = workbookOut;

			String sheetName = this.config.getSheetName();
			int dataArea = this.config.getDataArea();
			Workbook templateWorkbook = this.config.getTemplateWorkbook();
			Sheet templateSheet = templateWorkbook.getSheet(sheetName);
			this.rowOut = templateSheet.getRow(dataArea - 1);
			this.styles = buildCellStyles(templateWorkbook, this.workbookOut);
			this.cellRangeAddresses = new HashSet<CellRangeAddress>();
		}

		@Override
		public Boolean call(Map<String, Object> param) throws Exception {
			this.rowIndex++;
			String sheetName = this.config.getSheetName();
			int dataArea = this.config.getDataArea();
			int maxCol = this.config.getMaxCol();
			int sheetSize = this.config.getSheetSize();

			// rowIndex % sheetSize == 1 > 每个 sheet 页存放 sheetSize 条数据
			if (this.rowIndex % sheetSize == 1) {
				// 输出上一个 sheetOut 的模板尾
				outTemplateTail();

				this.sheetIndex++;
				this.sheetRowIndex = 1;

				// 构建 resourceSheetName
				String resourceSheetName = PlaceholderCommons.replacePlaceholder(sheetNameRule, new ParamMap<String, String>()
						.putParam(sheetNameInterKey, sheetName)
						.putParam(sheetNameNumberInterKey, CastCommons.numberToString(sheetIndex, 3)));
				// 创建 sheet 页
				this.sheetOut = this.workbookOut.createSheet(resourceSheetName);
				// 输当前 sheetOut 的模板尾
				outTemplateHead();
			} else {
				this.sheetRowIndex++;
			}

			int createRowIndex = (this.sheetRowIndex - 1) + (dataArea - 1);
			Row row = this.sheetOut.createRow(createRowIndex);
			for (short i = 0; i < maxCol; i++) {
				String field = "";
				Cell cellOut = this.rowOut.getCell(i);
				Cell cell = row.createCell(i);

				// 拷贝样式
				if (ValidateCommons.isNotEmpty(cellOut)) {
					field = cellOut.getStringCellValue();
					CellStyle cellStyleOut = cellOut.getCellStyle();
					if (ValidateCommons.isNotEmpty(cellStyleOut)) {
						cellStyleOut = this.styles.get(cellStyleOut.getIndex());
					}
					if (ValidateCommons.isNotEmpty(cellStyleOut)) {
						cell.setCellStyle(cellStyleOut);
					}
				}

				String value = PlaceholderCommons.replacePlaceholder(field, param);
				cell.setCellValue(value);
			}
			return true;
		}

		public void outTemplateHead() {
			int dataArea = this.config.getDataArea();
			copyTemplateFragment(0, dataArea - 1);
		}

		public void outTemplateTail() {
			if (this.sheetOut != null) {
				int dataRowIndex = this.sheetRowIndex - 1; // 转换到下标为 0
				int maxRow = this.config.getMaxRow();
				int dataArea = this.config.getDataArea();
				copyTemplateFragment(dataArea, maxRow);
				// 合并单元格
				for (CellRangeAddress cellRangeAddress : this.cellRangeAddresses) {
					int firstRow = cellRangeAddress.getFirstRow();
					int lastRow = cellRangeAddress.getLastRow();
					if (firstRow > dataArea - 1) {
						cellRangeAddress.setFirstRow(firstRow + dataRowIndex);
						cellRangeAddress.setLastRow(lastRow + dataRowIndex);
					}
					this.sheetOut.addMergedRegion(cellRangeAddress);
				}
			}
		}

		private void copyTemplateFragment(int startRowIndex, int endRowIndex) {
			int dataRowIndex = this.sheetRowIndex - 1; // 转换到下标为 0
			String sheetName = this.config.getSheetName();
			Workbook templateWorkbook = this.config.getTemplateWorkbook();
			Sheet templateSheet = templateWorkbook.getSheet(sheetName);
			for (int i = startRowIndex; i < endRowIndex; i++) {
				Row fromRow = templateSheet.getRow(i);
				Row toRow = this.sheetOut.createRow(i + dataRowIndex);
				copyRow(templateWorkbook, templateSheet, fromRow, this.workbookOut, this.sheetOut, toRow, i, this.styles, this.cellRangeAddresses);
			}
		}

	}

	/**
	 * <pre>
	 * 获取单元格的值并转化为字符串
	 * </pre>
	 * @param cell 单元格
	 * @return 转化为字符串的值
	 */
	public static String getCellValue(Cell cell) {
		String value = null;
		if (ValidateCommons.isNotEmpty(cell)) {
			int rowIndex = cell.getRowIndex();
			int colIndex = cell.getColumnIndex();
			CellType cellType = cell.getCellType();
			if (CellType.STRING == cellType) {
				value = cell.getStringCellValue();
			} else if (CellType.NUMERIC == cellType) {
				if (DateUtil.isCellDateFormatted(cell)) {
					value = dataFormatter.formatCellValue(cell);
				} else {
					value = CastCommons.numberToString(cell.getNumericCellValue());
				}
			} else if (CellType.FORMULA == cellType) {
				try {
					CellStyle style = cell.getCellStyle();
					CellFormat format = CellFormat.getInstance(style.getDataFormatString());
					CellFormatResult result = format.apply(cell);
					value = result.text;
				} catch (Exception e1) {
					Logger.warn(ExcelIOSimple.class, StringCommons.merger("单元格 [", rowIndex + 1, ", ", colIndex + 1, "] 公式解析失败，尝试按照数字格式取值"), e1);
					try {
						value = CastCommons.numberToString(cell.getNumericCellValue());
					} catch (IllegalStateException e2) {
						Logger.warn(ExcelIOSimple.class, StringCommons.merger("单元格 [", rowIndex + 1, ", ", colIndex + 1, "] 按照数字格式取值失败，尝试按照字符串格式取值"), e1);
						try {
							value = cell.getStringCellValue();
						} catch (IllegalStateException e3) {
							Logger.warn(ExcelIOSimple.class, StringCommons.merger("单元格 [", rowIndex + 1, ", ", colIndex + 1, "] 按照字符串格式取值失败，请检查公式定义"), e3);
						}
					}
				}
			} else if (CellType.ERROR == cellType) {
				value = "";
				Logger.warn(ExcelIOSimple.class, StringCommons.merger("单元格 [", rowIndex + 1, ", ", colIndex + 1, "] 检查到单元格错误，请检查单元格"));
			} else {
				value = cell.getRichStringCellValue().getString().trim();
			}
		}
		if (value == null) {
			value = "";
		}
		return value;
	}

	/**
	 * [参考 {@link #getDefaultCellStyle(Workbook, String)}]
	 * <pre>
	 * 默认 defaultType 为 {@link #DEFAULT_CELL_STYLE_STRING}
	 * </pre>
	 */
	public static CellStyle getDefaultCellStyle(Workbook workbook) {
		return getDefaultCellStyle(workbook, DEFAULT_CELL_STYLE_STRING);
	}

	/**
	 * <pre>
	 * 获取简单的样式
	 * </pre>
	 * @param workbook 工作簿
	 * @param defaultType [可空] [默认值为 {@link #DEFAULT_CELL_STYLE_STRING}] [参考 DEFAULT_CELL_STYLE_*]
	 * @return 样式
	 */
	public static CellStyle getDefaultCellStyle(Workbook workbook, String defaultType) {
		CellStyle style = workbook.createCellStyle();
		DataFormat dataFormat = workbook.createDataFormat();

		style.setBorderTop(BorderStyle.THIN);
		style.setBorderBottom(BorderStyle.THIN);
		style.setBorderLeft(BorderStyle.THIN);
		style.setBorderRight(BorderStyle.THIN);
		if (DEFAULT_CELL_STYLE_HEAD.equals(defaultType)) {
			Font font = workbook.createFont();
			font.setBold(true);

			style.setFont(font);
			style.setAlignment(HorizontalAlignment.CENTER);
			style.setDataFormat(dataFormat.getFormat("General"));
		} else if (DEFAULT_CELL_STYLE_STRING.equals(defaultType)) {
			style.setAlignment(HorizontalAlignment.LEFT);
			style.setDataFormat(dataFormat.getFormat("General"));
		} else if (DEFAULT_CELL_STYLE_INTEGER.equals(defaultType)) {
			style.setAlignment(HorizontalAlignment.RIGHT);
			style.setDataFormat(dataFormat.getFormat("0"));
		} else if (DEFAULT_CELL_STYLE_NUMBER.equals(defaultType)) {
			style.setAlignment(HorizontalAlignment.RIGHT);
			style.setDataFormat(dataFormat.getFormat("#,##0.00"));
		} else if (DEFAULT_CELL_STYLE_PERCENT.equals(defaultType)) {
			style.setAlignment(HorizontalAlignment.RIGHT);
			style.setDataFormat(dataFormat.getFormat("0.00%"));
		} else if (DEFAULT_CELL_STYLE_MONEY.equals(defaultType)) {
			style.setAlignment(HorizontalAlignment.RIGHT);
			style.setDataFormat(dataFormat.getFormat("$#,##0.00"));
		} else {
			style.setAlignment(HorizontalAlignment.LEFT);
			style.setDataFormat(dataFormat.getFormat("General"));
		}
		return style;
	}

	/**
	 * <pre>
	 * 以 fromWorkbook 为基准，在 toWorkbook 中构建所有 CellStyle
	 * </pre>
	 * @param fromWorkbook 样式来源工作簿
	 * @param toWorkbook 样式目标工作簿
	 * @return 在 toWorkbook 中构建的样式集合
	 */
	public static List<CellStyle> buildCellStyles(Workbook fromWorkbook, Workbook toWorkbook) {
		List<Font> fonts = new ArrayList<Font>();
		for (short i = 0; i < fromWorkbook.getNumberOfFonts(); i++) {
			Font fromFont = fromWorkbook.getFontAt(i);
			Font toFont = toWorkbook.createFont();
			toFont.setCharSet(fromFont.getCharSet());
			toFont.setFontName(fromFont.getFontName());
			toFont.setColor(fromFont.getColor());
			toFont.setFontHeight(fromFont.getFontHeight());
			toFont.setFontHeightInPoints(fromFont.getFontHeightInPoints());
			toFont.setBold(fromFont.getBold()); // 加粗
			toFont.setItalic(fromFont.getItalic()); // 斜体
			toFont.setUnderline(fromFont.getUnderline()); // 下划线
			toFont.setStrikeout(fromFont.getStrikeout()); // 删除线
			toFont.setTypeOffset(fromFont.getTypeOffset());
			fonts.add(toFont);
		}
		int fontSize = fonts.size();

		List<CellStyle> styles = new ArrayList<CellStyle>();
		for (short i = 0; i < fromWorkbook.getNumCellStyles(); i++) {
			CellStyle fromStyle = fromWorkbook.getCellStyleAt(i);
			CellStyle toStyle = toWorkbook.createCellStyle();

			// 拷贝字体
			int fontIndex = fromStyle.getFontIndex();
			if (fontSize > fontIndex) {
				Font toFont = fonts.get(fontIndex);
				if (ValidateCommons.isNotEmpty(toFont)) {
					toStyle.setFont(toFont);
				}
			}
			// 拷贝单元格样式
			toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor()); // 背景
			toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor()); // 前景
			toStyle.setFillPattern(fromStyle.getFillPattern()); // 填充为前景
			toStyle.setLocked(fromStyle.getLocked()); // 锁定单元格
			toStyle.setHidden(fromStyle.getHidden()); // 隐藏单元格
			// 拷贝文本样式
			toStyle.setDataFormat(fromStyle.getDataFormat()); // 数据格式
			toStyle.setAlignment(fromStyle.getAlignment()); // 水平对齐
			toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment()); // 垂直对其
			toStyle.setIndention(fromStyle.getIndention()); // 缩进
			toStyle.setWrapText(fromStyle.getWrapText()); // 包装设置（通过在多行上显示单元格，使单元格的所有内容都可见）
			toStyle.setRotation(fromStyle.getRotation()); // 旋转角度
			// 拷贝边框
			toStyle.setBorderTop(fromStyle.getBorderTop());
			toStyle.setBorderBottom(fromStyle.getBorderBottom());
			toStyle.setBorderLeft(fromStyle.getBorderLeft());
			toStyle.setBorderRight(fromStyle.getBorderRight());
			// 拷贝边框颜色
			toStyle.setTopBorderColor(fromStyle.getTopBorderColor());
			toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
			toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());
			toStyle.setRightBorderColor(fromStyle.getRightBorderColor());
			
			styles.add(toStyle);
		}
		return styles;
	}

	/**
	 * <pre>
	 * 拷贝单元格
	 * </pre>
	 * @param fromWorkbook 来源工作簿
	 * @param fromCell 来源单元格
	 * @param toWorkbook 目标工作簿
	 * @param toCell 目标单元格
	 */
	public static void copyCell(Workbook fromWorkbook, Cell fromCell, Workbook toWorkbook, Cell toCell) {
		CellStyle fromStyle = fromCell.getCellStyle();
		CellStyle toStyle = toCell.getCellStyle();
		if (ValidateCommons.isNotEmpty(fromStyle) && ValidateCommons.isNotEmpty(toStyle)) {
			String fromFormat = fromWorkbook.createDataFormat().getFormat(fromStyle.getDataFormat());
			short toFormat = toWorkbook.createDataFormat().getFormat(fromFormat);
			toStyle.setDataFormat(toFormat);
		}

		switch(fromCell.getCellType()) {
			case NUMERIC:
				toCell.setCellValue(fromCell.getNumericCellValue());
				break;
			case STRING:
				toCell.setCellValue(fromCell.getRichStringCellValue());
				break;
			case BOOLEAN:
				toCell.setCellValue(fromCell.getBooleanCellValue());
				break;
			case ERROR:
				toCell.setCellValue(fromCell.getErrorCellValue());
				break;
			case FORMULA:
				break;
			default:
				break;
		}
	}

	/**
	 * <pre>
	 * 拷贝行
	 * </pre>
	 * @param fromWorkbook 来源工作簿
	 * @param fromSheet 来源工作表
	 * @param fromRow 来源行
	 * @param toWorkbook 目标工作簿
	 * @param toSheet 目标工作表
	 * @param toRow 目标行
	 * @param rowIndex 行索引 [下标 0]
	 * @param styles 样式集
	 * @param cellRangeAddresses 单元格合并地址集
	 */
	public static void copyRow(Workbook fromWorkbook, Sheet fromSheet, Row fromRow, Workbook toWorkbook, Sheet toSheet, Row toRow, int rowIndex,
			List<CellStyle> styles, Set<CellRangeAddress> cellRangeAddresses) {
		// 拷贝行高
		toRow.setHeight(fromRow.getHeight());
		for (int i = 0; i < fromRow.getLastCellNum() + 1; i++) {
			// 拷贝列宽
			if (rowIndex <= 1) {
				int defaultWidth = fromSheet.getDefaultColumnWidth();
				int width = fromSheet.getColumnWidth(i);
				if (defaultWidth != width) {
					toSheet.setColumnWidth(i, width);
				}
			}

			// 拷贝布局（合并单元格）
			CellRangeAddress cellRangeAddress = getRegion(fromSheet, fromRow.getRowNum(), (short) i);
			if (ValidateCommons.isNotEmpty(cellRangeAddress)) {
				cellRangeAddresses.add(cellRangeAddress);
			}

			// 拷贝单元格
			Cell fromCell = fromRow.getCell(i);
			if (fromCell != null) {
				Cell toCell = toRow.createCell(i);
				toCell.setCellStyle(styles.get(fromCell.getCellStyle().getIndex()));
				copyCell(fromWorkbook, fromCell, toWorkbook, toCell);
			}
		}
	}

	/**
	 * <pre>
	 * 拷贝工作表
	 * </pre>
	 * @param fromWorkbook 来源工作簿
	 * @param fromSheet 来源工作表
	 * @param toWorkbook 目标工作簿
	 * @param toSheet 目标工作表
	 */
	public static void copySheet(Workbook fromWorkbook, Sheet fromSheet, Workbook toWorkbook, Sheet toSheet) {
		copySheet(fromWorkbook, fromSheet, toWorkbook, toSheet, null);
	}

	/**
	 * <pre>
	 * 拷贝工作表
	 * </pre>
	 * @param fromWorkbook 来源工作簿
	 * @param fromSheet 来源工作表
	 * @param toWorkbook 目标工作簿
	 * @param toSheet 目标工作表
	 * @param styles [可空] 样式集，如果为空则自动重新构建
	 */
	public static void copySheet(Workbook fromWorkbook, Sheet fromSheet, Workbook toWorkbook, Sheet toSheet, List<CellStyle> styles) {
		if (ValidateCommons.isEmpty(styles)) {
			styles = buildCellStyles(fromWorkbook, toWorkbook);
		}

		// 拷贝行边框
		int fromRowBreaks[] = fromSheet.getRowBreaks();
		if (ValidateCommons.isNotEmpty(fromRowBreaks)) {
			for (int fromRowBreak : fromRowBreaks) {
				toSheet.setRowBreak(fromRowBreak);
			}
		}
		// 拷贝列边框
		int[] fromColBreaks = fromSheet.getColumnBreaks();
		if (ValidateCommons.isNotEmpty(fromColBreaks)) {
			for (int fromColBreak : fromColBreaks) {
				toSheet.setColumnBreak(fromColBreak);
			}
		}
		// 拷贝打印设置
		PrintSetup fromPrintSetup = fromSheet.getPrintSetup();
		PrintSetup toPrintSetup = toSheet.getPrintSetup();
		BeanUtils.copyProperties(fromPrintSetup, toPrintSetup);

		Set<CellRangeAddress> cellRangeAddresses = new HashSet<CellRangeAddress>();
		for (int i = 0; i < fromSheet.getLastRowNum() + 1; i++) {
			Row fromRow = fromSheet.getRow(i);
			Row toRow = toSheet.createRow(i);
			if (ValidateCommons.isNotEmpty(fromRow)) {
				copyRow(fromWorkbook, fromSheet, fromRow, toWorkbook, toSheet, toRow, i, styles, cellRangeAddresses);
			}
		}
		// 合并单元格
		for (CellRangeAddress cellRangeAddress: cellRangeAddresses) {
			toSheet.addMergedRegion(cellRangeAddress);
		}
	}

	/**
	 * <pre>
	 * 拷贝工作簿
	 * </pre>
	 * @param fromWorkbook 来源工作簿
	 * @param toWorkbook 目标工作簿
	 */
	public static void copyWorkbook(Workbook fromWorkbook, Workbook toWorkbook) {
		List<CellStyle> styles = buildCellStyles(fromWorkbook, toWorkbook);

		for (int i = 0; i < fromWorkbook.getNumberOfSheets(); i++) {
			Sheet fromSheet = fromWorkbook.getSheetAt(i);
			Sheet toSheet = toWorkbook.createSheet(fromWorkbook.getSheetName(i));

			copySheet(fromWorkbook, fromSheet, toWorkbook, toSheet, styles);
		}
	}

	private static CellRangeAddress getRegion(Sheet sheet, int rowIndex, int colIndex) {
		for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
			CellRangeAddress region = sheet.getMergedRegion(i);
			if (region.getFirstRow() <= rowIndex && region.getLastRow() >= rowIndex && region.getFirstColumn() <= colIndex && region.getLastColumn() >= colIndex) {
				return new CellRangeAddress(region.getFirstRow(), region.getLastRow(), region.getFirstColumn(), region.getLastColumn());
			}
		}
		return null;
	}

}
